﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;

namespace ReportsApp
{
    public static class DBHelper
    {
        public static bool IsBranchData = false;//{ get; set; }

        //private static string mainCS = ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString;
        //private static string salariesCS = ConfigurationManager.ConnectionStrings["SalariesConnectionString"].ConnectionString;
        //private static string financialCS = ConfigurationManager.ConnectionStrings["FinancialConnectionString"].ConnectionString;

        private static string mainCS = "Initial Catalog=MainBilling;Data Source=182.18.135.133; User ID=vijay;Password=Ishanvi@123;Connection Timeout=5000;";
        private static string salariesCS = "Initial Catalog=KSSalaries;Data Source=182.18.135.133;User ID=vijay;Password=Ishanvi@123";
        private static string financialCS = "Initial Catalog=KSFinancial;Data Source=182.18.135.133;User ID=vijay;Password=Ishanvi@123";

        private static string chandanagarCS = string.Empty;
        private static string ameerpetCS = string.Empty;
        private static string jntuCS = string.Empty;
        private static string kukatpallyCS = string.Empty;
        private static string miyapurCS = string.Empty;

        private static string csc1Con = string.Empty;
        private static string csc2Con = string.Empty;
        private static string csc3Con = string.Empty;

        private static string sangareddyCon = string.Empty;

        //private static string chandanagarCS = ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString;
        //private static string ameerpetCS = ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString;
        //private static string jntuCS = ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ConnectionString;
        //private static string kukatpallyCS = ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString;
        //private static string miyapurCS = ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString;

        //private static string csc1Con = ConfigurationManager.ConnectionStrings["CSC1ConnectionString"].ConnectionString;
        //private static string csc2Con = ConfigurationManager.ConnectionStrings["CSC2ConnectionString"].ConnectionString;
        //private static string csc3Con = ConfigurationManager.ConnectionStrings["CSC3ConnectionString"].ConnectionString;

        //private static string sangareddyCon = ConfigurationManager.ConnectionStrings["SangareddyConnectionString"].ConnectionString;

        //private static SqlConnection mainCS = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnectionString"].ConnectionString);
        //private static SqlConnection kukatpallyCS = new SqlConnection(ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString);
        //private static SqlConnection miyapurCS = new SqlConnection(ConfigurationManager.ConnectionStrings["KukatpallyConnectionString"].ConnectionString);
        //private static SqlConnection salariesCS = new SqlConnection(ConfigurationManager.ConnectionStrings["MiyapurConnectionString"].ConnectionString);

        //private static SqlConnection chandanagarCS = new SqlConnection(ConfigurationManager.ConnectionStrings["ChandanagarConnectionString"].ConnectionString);
        //private static SqlConnection ameerpetCS = new SqlConnection(ConfigurationManager.ConnectionStrings["AmeerpetConnectionString"].ConnectionString);
        //private static SqlConnection jntuCS = new SqlConnection(ConfigurationManager.ConnectionStrings["JNTUConnectionString"].ConnectionString);

        //Branches
        //1 - Chandanagar
        //2 - Miyapur
        //3 - Kukatpally
        //4 - Ameerpet
        //5 - JNTU

        #region Actual Reports

        internal static DataSet GetFinancialReport(DateTime dateTime, string branchID)
        {
            SqlConnection con = null;
            try
            {
                StringBuilder str = new StringBuilder();

                //{2}
                string BILLTABLE = "Bill";
                
                //{3}
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dateTime.Month && DateTime.Now.Year == dateTime.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                }

                //POS Sales - 0
                //str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN Bill B ON B.BillNo = BD.BillNo WHERE DateDiff(dd, BD.Date, '{0}') = 0  AND B.IsBillCancelled = 0  AND B.IsSpecialBill = 0 ");
                str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM {3} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN {2} B ON B.BillNo = BD.BillNo WHERE DateDiff(dd, BD.Date, '{0}') = 0 AND DateDiff(dd, B.BillDate, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND BD.Branch = {1} AND B.Branch = {1} ");
                str.Append(" GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");

                //POS Total Sales & POS Discounts - 1
                str.Append("SELECT ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {2} WHERE DateDiff(dd, {2}.BillDate, '{0}') = 0 AND IsBillCancelled = 0  AND IsSpecialBill = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Cake Advances & Cake Discounts - 2
                str.Append("SELECT ROUND(SUM(Advance + DiscountByCost), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND DateDiff(dd, BookedDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Cake Balances - 3
                str.Append("SELECT ROUND(SUM(Balance), 2) AS Balance FROM Cake WHERE IsCancelled = 0 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0)) ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Expenses - 4
                str.Append("SELECT ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Sodexho & Creditcard Sales - 5
                str.Append("SELECT SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1};");

                //Credit Note Amount - 6
                str.Append("SELECT ROUND(SUM(Amount),2) AS CreditNoteAmount FROM CreditNote CN JOIN {2} B ON CN.BillNo = B.BillNo WHERE DateDiff(DD, CN.Date, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND CN.Branch = {1} AND B.Branch = {1};");

                //Vendor Bills Amount - 7
                str.Append("SELECT ROUND(SUM(Amount),2) AS VendorSales FROM VendorSales VS JOIN {2} B ON B.BillNo = VS.BillNo WHERE DateDiff(DD, VS.Date, '{0}') = 0 AND DateDiff(DD, B.BillDate, '{0}') = 0 AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND VS.Branch = {1} AND B.Branch = {1};");

                //Bulk Orders Amount - 8
                str.Append(" SELECT SUM(Advance) AS BulkOrderAdvance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND DateDiff(DD, BO.BookedDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1};");
                
                //Bulk Orders Balance - 9
                str.Append(" SELECT SUM(Balance) AS BulkOrderBalance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND ((BO.DeliveredDate IS NULL AND DATEDIFF(DD, BO.DeliveryDate, '{0}') = 0) OR (BO.DeliveredDate IS NOT NULL AND DATEDIFF(DD, BO.DeliveredDate, '{0}') = 0)) ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1};");

                //Cashier Wise POS Sales and POS Discounts - 10
                str.Append("SELECT B.[USER] AS ID, E.EmpName AS Cashier, ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {2} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE DateDiff(dd, B.BillDate, '{0}') = 0 AND IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND B.Branch = {1}");
                str.Append(" GROUP BY B.[USER], E.EmpName;");

                //Cashier Wise Expenses - 11
                str.Append("SELECT Cashier, ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Cashier Wise Sodexho & Credit Card Amounts - 12
                str.Append("SELECT Cashier, SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE DATEDIFF(DD, Date, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Cake Advances By Cashier - 13
                str.Append("SELECT Cashier, ROUND(SUM(Advance), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND DateDiff(dd, BookedDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //Actual Cake Balances - 14
                str.Append("SELECT CashierDelivered AS Cashier, ROUND(SUM(Balance), 2) AS CakeBalances FROM Cake WHERE IsCancelled = 0 AND CashierDelivered <> 0 AND DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY CashierDelivered;");

                //BulkOrder Advances By Cashier - 15
                str.Append("SELECT Cashier, ROUND(SUM(Advance), 2) AS Advance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND DateDiff(dd, BO.BookedDate, '{0}') = 0");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {1}");
                str.Append(" GROUP BY Cashier;");

                //BulkOrder Balances - 16
                str.Append("SELECT CashierDelivered AS Cashier, ROUND(SUM(Balance), 2) AS Balance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND BO.IsOrderDelivered = 1 AND DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {1}");
                str.Append(" GROUP BY CashierDelivered;");

                if (IsBranchData)
                {
                    if (branchID == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branchID == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branchID == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branchID == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branchID == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branchID == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branchID == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branchID == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branchID == "9")
                        con = new SqlConnection(sangareddyCon);

                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), dateTime.ToString("MM/dd/yyyy"), branchID, BILLTABLE, BILLDETAILTABLE);
                        cmd.CommandTimeout = con.ConnectionTimeout;
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
                else
                {
                    con = new SqlConnection(mainCS);
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), dateTime.ToString("MM/dd/yyyy"), branchID, BILLTABLE, BILLDETAILTABLE);
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
            }
            catch (Exception) { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetMonthlyReport(DateTime dt, string branchID)
        {
            SqlConnection con = null;
            try
            {
                StringBuilder str = new StringBuilder();

                //{4}
                string BILLTABLE = "Bill";
                //{5}
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dt.Month && DateTime.Now.Year == dt.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dt.ToString("MMM"), dt.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dt.ToString("MMM"), dt.Year.ToString());
                }

                //POS Sales
                //str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM {5} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN {4} B ON B.BillNo = BD.BillNo WHERE MONTH(BD.Date) = {0} AND YEAR(BD.Date) = {1} AND B.IsBillCancelled = 0 ");
                //if (branchID != "0")
                //    str.Append(" AND BD.Branch = {2} AND B.Branch = {2} GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");
                //else
                //    str.Append(" GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");

                str.Append(@"SELECT D.DeptCode, D.DeptName, ROUND(SUM(Qty), 2) AS Qty, ROUND(SUM(BD.Qty * MI.SellingPrice),2) AS Sales FROM {5} BD
                            JOIN MasterItems MI ON BD.Item = MI.ItemCode
                            JOIN Department D ON D.DeptID = MI.Dept
                            INNER JOIN
                            (
	                                SELECT ID, BillNo, TotalAmount, BillDate AS [Date] FROM {4} WHERE "); 
                                    if (branchID != "0")
                                    {
                                        str.Append(" Branch = {2} AND ");
                                    }
                                    str.Append(@" IsBillCancelled = 0 
		                                UNION ALL
	                                SELECT ID, BillNo, TotalAmount, BookedDate AS [Date] FROM BulkOrder WHERE ");
                                    if (branchID != "0")
                                    {
                                        str.Append(" Branch = {2} AND "); 
                                    }
                                    str.Append(@" IsOrderCancelled = 0
                            ) Main
                            ON Main.BillNo = BD.BillNo AND CONVERT(DATE, BD.[Date]) = CONVERT(DATE, Main.[Date]) ");
                            if (branchID != "0")
                            {
                                str.Append(" WHERE BD.Branch = {2}");
                            }
                            str.Append(" GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");

                //POS Total Sales & POS Discounts
                str.Append("SELECT ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM {4} WHERE MONTH({4}.BillDate) = {0} AND YEAR({4}.BillDate) = {1} AND IsBillCancelled = 0 AND IsSpecialBill = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(" ;");

                //Cake Advances & Cake Discounts
                str.Append("SELECT ROUND(SUM(Advance + DiscountByCost), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND MONTH(BookedDate) = {0} AND YEAR(BookedDate) = {1}");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Cake Balances
                str.Append("SELECT ROUND(SUM(Balance), 2) AS Balance FROM Cake WHERE IsCancelled = 0 AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {0} AND YEAR(DeliveryDate) = {1}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {0} AND YEAR(DeliveredDate) = {1}))");
                //str.Append("SELECT ROUND(SUM(Balance), 2) AS Balance FROM Cake WHERE MONTH(DeliveredDate) = {0} AND YEAR(DeliveredDate) = {1} AND Branch = {2};");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Expenses
                str.Append("SELECT ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE MONTH(Date) = {0} AND YEAR(Date) = {1}");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Sodexho & Creditcard Sales
                str.Append("SELECT SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE MONTH(Date) = {0} AND YEAR(Date) = {1} ");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Credit Note Amount
                str.Append("SELECT ROUND(SUM(Amount),2) AS CreditNoteAmount FROM CreditNote CN JOIN {4} B ON CN.BillNo = B.BillNo WHERE MONTH(CN.Date) = {0} AND YEAR(CN.Date) = {1} AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND CN.Branch = {2} AND B.Branch = {2};");
                else
                    str.Append(";");

                //Vendor Bills Amount - 7
                str.Append("SELECT ROUND(SUM(Amount),2) AS VendorSales FROM VendorSales VS JOIN {4} B ON B.BillNo = VS.BillNo WHERE MONTH(VS.Date) = {0} AND YEAR(VS.Date) = {1} AND MONTH(B.BillDate) = {0} AND YEAR(B.BillDate) = {1} AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND VS.Branch = {2} AND B.Branch = {2};");
                else
                    str.Append(";");
                
                //Bulk Orders Amount - 8
                str.Append(" SELECT SUM(Advance) AS BulkOrderAdvance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND MONTH(BO.BookedDate) = {0} AND YEAR(BO.BookedDate) = {1} ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {2};");
                else
                    str.Append(";");
                
                // - 9
                str.Append(" SELECT SUM(Balance) AS BulkOrderBalance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND MONTH(BO.BookedDate) = {0} AND YEAR(BO.BookedDate) = {1} ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {2};");
                else
                    str.Append(";");
                
                if (IsBranchData)
                {
                    /*using (SqlCommand cmd = new SqlCommand(string.Format(str.ToString(), dt.Month, dt.Year, branchID), kukatpallyCS))
                    {
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }*/
                    if (branchID == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branchID == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branchID == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branchID == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branchID == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branchID == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branchID == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branchID == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branchID == "9")
                        con = new SqlConnection(sangareddyCon);

                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), dt.Month, dt.Year, branchID, BILLTABLE, BILLDETAILTABLE);
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
                else
                {
                    /*using (SqlCommand cmd = new SqlCommand(string.Format(str.ToString(), dt.Month, dt.Year, branchID), mainCS))
                    {
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }*/
                    con = new SqlConnection(mainCS);
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), dt.Month, dt.Year, branchID, branchID, BILLTABLE, BILLDETAILTABLE);
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetStockByBranch(string branch, DateTime dt)
        {
            SqlConnection con = null;
            try
            {
                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);

                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "SELECT ItemCode, ItemName, ROUND(S.StockAvailable, 2) AS StockAvailable FROM MasterItems MI JOIN Stock S ON MI.StockID = S.StockID";
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            if (ds != null && ds.Tables[0].Rows.Count > 0)
                                return ds.Tables[0];
                        }
                    }
                }
                else
                {
                    con = new SqlConnection(mainCS);

                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    using (SqlCommand cmd = new SqlCommand(string.Format("SELECT RS.ItemCode, MI.ItemName, ROUND(RS.Stock, 2) AS StockAvailable FROM RemainingStock RS JOIN MasterItems MI ON RS.ItemCode = MI.ItemCode WHERE DATEDIFF(DD, RS.Date, '{0}') = 0 AND Branch = {1}", dt.ToString("MM/dd/yyyy"), branch), con))
                    {
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            if (ds != null && ds.Tables[0].Rows.Count > 0)
                                return ds.Tables[0];
                        }
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetExpenseByBranchAndDate(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string str;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT E.Expense AS ID, ME.ExpenseItem, E.Description, E.Amount, CONVERT(VARCHAR, E.Date, 106) AS Date FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE E.Branch = {0} AND MONTH(E.Date) = {1} AND YEAR(E.Date) = {2};SELECT SUM(E.Amount) FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE E.Branch = {0} AND MONTH(E.Date) = {1} AND YEAR(E.Date) = {2}", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT E.Expense AS ID, ME.ExpenseItem, E.Description, E.Amount FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE E.Branch = {0} AND DATEDIFF(DAY, '{1}', E.Date) = 0; SELECT SUM(E.Amount) FROM Expense E JOIN MasterExpenses ME ON E.Expense = ME.ID WHERE E.Branch = {0} AND DATEDIFF(DAY, '{1}', E.Date) = 0", branch, date.ToString("MM/dd/yyyy"));
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = str;

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }

                /*using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }*/
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetCreditNotes(string branch, DateTime date, bool forWholeMonth, string company, out bool IsException)
        {
            SqlConnection con = null;
            try
            {
                IsException = false;
                string str;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT MCN.CreditNoteCompany, CN.BillNo, CN.Amount, CONVERT(VARCHAR, CN.Date, 106) AS Date FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND MONTH(CN.Date) = {1} AND YEAR(CN.Date) = {2} AND CN.CreditNoteCompany = {3} AND B.IsBillCancelled = 0; SELECT SUM(CN.Amount) FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND MONTH(CN.Date) = {1} AND YEAR(CN.Date) = {2} AND CN.CreditNoteCompany = {3} AND B.IsBillCancelled = 0;", branch, date.Month, date.Year, company);
                }
                else
                {
                    str = string.Format("SELECT MCN.CreditNoteCompany, CN.BillNo, CN.Amount FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND DateDiff(dd, CN.Date, '{1}') = 0 AND CN.CreditNoteCompany = {2} AND B.IsBillCancelled = 0; SELECT SUM(CN.Amount) FROM CreditNote CN JOIN MasterCreditNotes MCN ON CN.CreditNoteCompany = MCN.ID JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND DateDiff(dd, CN.Date, '{1}') = 0 AND CN.CreditNoteCompany = {2} AND B.IsBillCancelled = 0", branch, date.ToString("MM/dd/yyyy"), company);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                /*using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }*/
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = str;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataSet GetDamages(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            try
            {
                IsException = false;
                string str;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT D.ItemCode, MI.ItemName, D.Qty, (D.Qty * MI.CostPrice) AS Amount, CONVERT(VARCHAR, D.Date, 106) AS Date FROM Damage D JOIN MasterItems MI ON D.ItemCode = MI.ItemCode WHERE Branch = {0} AND MONTH(D.Date) = {1} AND YEAR(D.Date) = {2};SELECT SUM(D.Qty * MI.CostPrice) AS TotalAmount FROM Damage D JOIN MasterItems MI ON D.ItemCode = MI.ItemCode WHERE Branch = {0} AND MONTH(D.Date) = {1} AND YEAR(D.Date) = {2}", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT D.ItemCode, MI.ItemName, D.Qty, (D.Qty * MI.CostPrice) AS Amount FROM Damage D JOIN MasterItems MI ON D.ItemCode = MI.ItemCode WHERE Branch = {0} AND DATEDIFF(DD, D.Date, '{1}') = 0;SELECT SUM(D.Qty * MI.CostPrice) AS TotalAmount FROM Damage D JOIN MasterItems MI ON D.ItemCode = MI.ItemCode WHERE Branch = {0} AND DATEDIFF(DD, D.Date, '{1}') = 0;", branch, date.ToString("MM/dd/yyyy"));
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                /*using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }*/
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = str;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetCancelledBills(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }


                IsException = false;
                string str;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, E.EmpName AS Cashier, (CONVERT(VARCHAR, BillDate, 106) + SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Date, CancelledReason, BillDate FROM {3} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE IsBillCancelled = 1 AND B.Branch = {0} AND MONTH(BillDate) = {1} AND YEAR(BillDate) = {2}", branch, date.Month, date.Year, BILLTABLE);
                }
                else
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, E.EmpName AS Cashier, (SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Time, CancelledReason, BillDate FROM {2} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE IsBillCancelled = 1 AND DATEDIFF(DD, BillDate, '{0}') = 0 AND B.Branch = {1}", date.ToString("MM/dd/yyyy"), branch, BILLTABLE);
                }

                //if (con.State == ConnectionState.Closed)
                //    con.Open();

                /*using (SqlCommand cmd = new SqlCommand(str, kukatpallyCS))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }*/

                using (SqlDataAdapter adapter = new SqlDataAdapter(str, con))
                {
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        return ds.Tables[0];
                }

                //using (SqlCommand cmd = con.CreateCommand())
                //{
                //    cmd.CommandText = str;
                //    cmd.CommandType = CommandType.Text;
                //    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                //    {
                //        DataSet ds = new DataSet();
                //        adapter.Fill(ds);
                //        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                //            return ds.Tables[0];
                //    }
                //}
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetBillDetailsByBillNo(string billNo, string branch, DateTime date, out bool IsException)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                IsException = false;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                using (SqlCommand cmd = new SqlCommand("SELECT BillNo, Item AS ItemCode, MI.ItemName, BD.Qty FROM " + BILLDETAILTABLE + " BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE DateDiff(dd, BD.Date, '" + date.ToString("MM/dd/yyyy") + "') = 0 AND BillNo = '" + billNo + "'", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetDiscountedBills(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                string str;
                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, E.EmpName AS Cashier, (CONVERT(VARCHAR, BillDate, 106) + SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Date, DiscountReason, BillDate FROM {3} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE (DiscountByPercentage <> 0 OR DiscountByCost <> 0) AND B.Branch = {0} AND MONTH(BillDate) = {1} AND YEAR(BillDate) = {2} AND IsBillCancelled = 0", branch, date.Month, date.Year, BILLTABLE);
                }
                else
                {
                    str = string.Format("SELECT BillNo, DiscountByPercentage AS '% Disc', DiscountByCost AS 'Disc Amt', TotalAmount AS Amount, TotalNetAmount AS FinalAmt, E.EmpName AS Cashier, (SUBSTRING(CONVERT(varchar, BillDate, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, BillDate, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, BillDate, 100), 18, 2)) AS Time, DiscountReason, BillDate FROM {2} B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE (DiscountByPercentage <> 0 OR DiscountByCost <> 0) AND DATEDIFF(DD, BillDate, '{0}') = 0 AND B.Branch = {1} AND IsBillCancelled = 0", date.ToString("MM/dd/yyyy"), branch, BILLTABLE);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetAllCompaniesByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCS);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCS);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCS);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCS);
                else if (branch == "5")
                    con = new SqlConnection(jntuCS);
                else if (branch == "6")
                    con = new SqlConnection(csc1Con);
                else if (branch == "7")
                    con = new SqlConnection(csc2Con);
                else if (branch == "8")
                    con = new SqlConnection(csc3Con);
                else if (branch == "9")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCS);

                //if (con.State == ConnectionState.Closed)
                //    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, CreditNoteCompany FROM MasterCreditNotes", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;
        }

        internal static DataSet GetVendorSales(string branch, DateTime date, bool forWholeMonth, string vendor, out bool IsException)
        {
            SqlConnection con = null;
            try
            {
                IsException = false;
                string str;

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    if (vendor == "0")
                        str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND MONTH(VS.Date) = {1} AND YEAR(VS.Date) = {2} AND B.IsBillCancelled = 0;SELECT SUM(VS.Amount) FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND MONTH(VS.Date) = {1} AND YEAR(VS.Date) = {2} AND B.IsBillCancelled = 0;", branch, date.Month, date.Year);
                    else
                        str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND MONTH(VS.Date) = {1} AND YEAR(VS.Date) = {2} AND VS.Vendor = {3} AND B.IsBillCancelled = 0;SELECT SUM(VS.Amount) FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND MONTH(VS.Date) = {1} AND YEAR(VS.Date) = {2} AND VS.Vendor = {3} AND B.IsBillCancelled = 0;", branch, date.Month, date.Year, vendor);
                }
                else
                {
                    if (vendor == "0")
                        str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND DateDiff(dd, VS.Date, '{1}') = 0 AND B.IsBillCancelled = 0 AND DateDiff(dd, B.BillDate, '{1}') = 0;SELECT SUM(VS.Amount) FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND DateDiff(dd, VS.Date, '{1}') = 0 AND B.IsBillCancelled = 0 AND DateDiff(dd, B.BillDate, '{1}') = 0;", branch, date.ToString("MM/dd/yyyy"));
                    else
                        str = string.Format("SELECT MV.Vendor, VS.BillNo, VS.Amount, CONVERT(VARCHAR, VS.Date, 106) AS Date FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND DateDiff(dd, VS.Date, '{1}') = 0 AND VS.Vendor = {2} AND B.IsBillCancelled = 0 AND DateDiff(dd, B.BillDate, '{1}') = 0;SELECT SUM(VS.Amount) FROM VendorSales VS JOIN MasterVendors MV ON VS.Vendor = MV.ID JOIN Bill B ON VS.BillNo = B.BillNo WHERE VS.Branch = {0} AND DateDiff(dd, VS.Date, '{1}') = 0 AND VS.Vendor = {2} AND B.IsBillCancelled = 0  AND DateDiff(dd, B.BillDate, '{1}') = 0;", branch, date.ToString("MM/dd/yyyy"), vendor);
                }

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = str;
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetAllVendorsByBranch(string branch)
        {
            SqlConnection con = null;

            try
            {
                if (branch == "1")
                    con = new SqlConnection(chandanagarCS);
                else if (branch == "2")
                    con = new SqlConnection(miyapurCS);
                else if (branch == "3")
                    con = new SqlConnection(kukatpallyCS);
                else if (branch == "4")
                    con = new SqlConnection(ameerpetCS);
                else if (branch == "5")
                    con = new SqlConnection(jntuCS);
                else if (branch == "6")
                    con = new SqlConnection(csc1Con);
                else if (branch == "7")
                    con = new SqlConnection(csc2Con);
                else if (branch == "8")
                    con = new SqlConnection(csc3Con);
                else if (branch == "9")
                    con = new SqlConnection(sangareddyCon);
                else if (branch == "0" || branch == "-1" || branch == "-2")
                    con = new SqlConnection(mainCS);

                //if (con.State == ConnectionState.Closed)
                //    con.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT ID, Vendor FROM MasterVendors", con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                    con.Close();
            }
            return null;
        }

        #endregion

        #region Main

        internal static DataSet GetEmpAdvancesandBills(DateTime dateTime, string branchID)
        {
            SqlConnection con = new SqlConnection(salariesCS);
            try
            {
                string query = "SELECT ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE DATEDIFF(DD, AddedDate, '{0}') = 0 ";
                if (branchID != "0")
                    query += " AND FromBranch = {1} ";
                query += " AND TransactionType IN (1,2);";

                //Cashier Wise Debit Transactions
                query += "SELECT Cashier, ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE DATEDIFF(DD, AddedDate, '{0}') = 0 ";
                if (branchID != "0")
                    query += " AND FromBranch = {1} ";
                query += " AND TransactionType IN (1,2) GROUP BY Cashier;";

                using (SqlCommand cmd = new SqlCommand(string.Format(query, dateTime.ToString("MM/dd/yyyy"), branchID), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        //if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        //    return double.Parse(ds.Tables[0].Rows[0]["EmployeeDebitAmount"].ToString());
                        return ds;
                    }
                }
            }
            catch (Exception) { }
            return null;
        }

        internal static double GetEmpAdvancesandBillsForMonth(DateTime dateTime, string branchID)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(salariesCS);
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE MONTH(AddedDate) = {0} AND YEAR(AddedDate) = {1} AND FromBranch IN ({2}) AND TransactionType IN (1,2)", dateTime.Month, dateTime.Year, (branchID == "0" ? "1,2,3,4,5" : branchID)), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return double.Parse(ds.Tables[0].Rows[0]["EmployeeDebitAmount"].ToString());
                    }
                }
            }
            catch (Exception) { }
            return 0d;
        }

        internal static double GetEmpAdvancesandBillsForMonth(DateTime fromDate, DateTime toDate, string branchID)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(salariesCS);
                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT ROUND(SUM(Amount),2) AS EmployeeDebitAmount FROM DebitTransactions WHERE AddedDate >= '{0}' AND AddedDate <= '{1}' AND FromBranch IN ({2}) AND TransactionType IN (1,2)", fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), (branchID == "0" ? "1,2,3,4,5" : branchID)), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return double.Parse(ds.Tables[0].Rows[0]["EmployeeDebitAmount"].ToString());
                    }
                }
            }
            catch (Exception) { }
            return 0d;
        }

        internal static DataTable GetBranches()
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT BranchID, BranchName FROM MasterBranches", mainCS);
                adapter.Fill(ds, "Branches");
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetDepartments()
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT DeptID, DeptName FROM Department", mainCS);
                adapter.Fill(ds, "Departments");
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        #endregion

        internal static DataTable GetBulkOrders(string branch, DateTime date, bool forWholeMonth, out bool IsException)
        {
            SqlConnection con = null;

            try
            {
                IsException = false;

                string str;

                if (forWholeMonth)
                {
                    str = string.Format("SELECT BillNo, B.PhoneNo, TotalAmount, DiscountByCost AS 'Disc(Rs)', DiscountByPercentage AS 'Disc(%)', CONVERT(VARCHAR, BookedDate, 106) AS BookedDate, CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled, CancelledReason, E.EmpName AS Cashier FROM BulkOrder B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE B.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2}", branch, date.Month, date.Year);
                }
                else
                {
                    str = string.Format("SELECT BillNo, B.PhoneNo, TotalAmount, DiscountByCost AS 'Disc(Rs)', DiscountByPercentage AS 'Disc(%)', CONVERT(VARCHAR, DeliveryDate, 106) AS DeliveryDate, IsOrderCancelled, CancelledReason, E.EmpName AS Cashier FROM BulkOrder B JOIN KSSalaries.dbo.Employees E ON B.[User] = E.EmpID WHERE DATEDIFF(DD, BookedDate, '{0}') = 0 AND B.Branch = {1}", date.ToString("MM/dd/yyyy"), branch);
                }

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            return null;
        }

        internal static DataSet GetCakesReport(string branch, DateTime date, bool forWholeMonth, string category, out bool IsException)
        {
            SqlConnection con = null;
            IsException = false;
            try
            {
                string str = string.Empty;
                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                if (forWholeMonth)
                {
                    if (category == "General")
                    //if (!isCancelledCakes)
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Cancelled")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 1 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Undelivered")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }
                    else if (category == "Discounted")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {0} AND MONTH(BookedDate) = {1} AND YEAR(BookedDate) = {2};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2};";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = {0} AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {0} AND ((DeliveredDate IS NULL AND MONTH(DeliveryDate) = {1} AND YEAR(DeliveryDate) = {2}) OR (DeliveredDate IS NOT NULL AND MONTH(DeliveredDate) = {1} AND YEAR(DeliveredDate) = {2}))";
                    }

                    str = string.Format(str, branch, date.Month, date.Year);
                }
                else
                {
                    //if (!isCancelledCakes)
                    if (category == "General")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND IsCakeDelivered = 1 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 1 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Cancelled")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID JOIN KSSalaries.dbo.Employees E ON C.Cashier = E.EmpID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Undelivered")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 1 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.IsCakeDelivered = 0 AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 1 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND IsCakeDelivered = 0 AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    else if (category == "Discounted")
                    {
                        str = "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {1} AND DATEDIFF(DD, BookedDate, '{0}') = 0 ;";
                        str += "SELECT SUM(Advance) AS Advance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, BookedDate, '{0}') = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {1};";

                        //str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND C.Branch = {1} AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 ;";
                        str += "SELECT C.CakeNo, C.CustomerName, C.PhoneNo, CONVERT(VARCHAR, C.DeliveryDate, 106) AS DeliveryDate, C.DeliveryTime, CS.CakeShape AS Shape, C.AlbumModelDesc AS AlbumModel, C.Message, CF.Flavour, C.Qty, C.Advance, C.Balance, C.NetAmount, C.DiscountByCost, C.DiscountInPercentage, C.ExtraPercentage AS 'Extra %', C.ExtraValue, C.BookedDate, C.DeliveredDate, C.DiscountReason, C.IsChefCopyPrinted, E.EmpName AS Cashier FROM Cake C LEFT JOIN CakeShapes CS ON C.Shape = CS.ID LEFT JOIN CakeFlavours CF ON C.Flavour = CF.ID WHERE C.IsCancelled = 0 AND (C.DiscountInPercentage <> 0 OR C.DiscountByCost <> 0) AND C.Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));";

                        //str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND DATEDIFF(DD, DeliveredDate, '{0}') = 0 AND Branch = {1}";
                        str += "SELECT SUM(Balance) AS Balance, SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND (DiscountInPercentage <> 0 OR DiscountByCost <> 0) AND Branch = {1} AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0))";
                    }
                    str = string.Format(str, date.ToString("MM-dd-yyyy"), branch);
                }

                using (SqlCommand cmd = new SqlCommand(str, con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
                            return ds;
                    }
                }
            }
            catch (Exception)
            {
                IsException = true;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataRow GetActualAmount(DateTime date, string branch)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT ActualCashAvailable, Difference FROM AvailableCash WHERE DateDiff(dd, Date, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch), mainCS);
                adapter.Fill(ds, "Branches");
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    return ds.Tables[0].Rows[0];
            }
            catch
            { }
            return null;
        }

        internal static int SaveAvailableCash(DateTime date, string branch, double appCashAvailable, double actualCashAvilable, double difference)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCS);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrModifyAvailableCash '{0}', '{1}', {2}, {3}, {4}", date.ToString("MM/dd/yyyy"), branch, appCashAvailable, actualCashAvilable, difference), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return 0;
        }

        internal static DataTable GetLoginsByBranch(string branch, DateTime dateTime)
        {
            SqlConnection con = null;

            try
            {
                //if (branch == "1")
                //    con = new SqlConnection(chandanagarCS);
                //else if (branch == "2")
                //    con = new SqlConnection(miyapurCS);
                //else if (branch == "3")
                //    con = new SqlConnection(kukatpallyCS);
                //else if (branch == "4")
                //    con = new SqlConnection(ameerpetCS);
                //else if (branch == "5")
                //    con = new SqlConnection(jntuCS);
                //else if (branch == "6")
                //    con = new SqlConnection(csc1Con);
                //else if (branch == "7")
                //    con = new SqlConnection(csc2Con);
                //else if (branch == "8")
                //    con = new SqlConnection(csc3Con);
                //else if (branch == "9")
                //    con = new SqlConnection(sangareddyCon);

                con = new SqlConnection(mainCS);

                using (SqlCommand cmd = new SqlCommand(string.Format("SELECT UserName, SUBSTRING(CONVERT(varchar, LoginTime, 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar, LoginTime, 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, LoginTime, 100), 18, 2) AS Time FROM LoginDetails WHERE DATEDIFF(DD, LoginTime, '{0}') = 0 AND Branch = '{1}' ORDER BY ID ", dateTime.ToString("MM/dd/yyyy"), branch), con))
                {
                    cmd.CommandType = CommandType.Text;
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        if (ds != null && ds.Tables != null && ds.Tables[0].Rows.Count > 0)
                            return ds.Tables[0];
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            return null;
        }

        internal static DataTable GetItemWiseReportByBranch(string branch, DateTime dateTime, string itemCodeOrName, bool isMonthly)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dateTime.Month && DateTime.Now.Year == dateTime.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dateTime.ToString("MMM"), dateTime.Year.ToString());
                }

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                string query = string.Empty;

                if (!isMonthly)
                {
                    //query = "SELECT BD.Item AS Code, MI.ItemName, MI.SellingPrice AS Price, ROUND(SUM(Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales FROM {4} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode JOIN {3} B ON BD.BillNo = B.BillNo WHERE (BD.Item like '{0}%' OR MI.ItemName like '{0}%') AND B.IsBillCancelled = 0 ";
                    //if (branch != "0")
                    //    query += " AND B.Branch = {1} AND BD.Branch = {1} ";
                    //query += " AND DATEDIFF(DAY, '{2}', BD.Date) = 0 AND DATEDIFF(DD, '{2}', B.BillDate) = 0 GROUP BY BD.Item, MI.ItemName, MI.SellingPrice ORDER BY BD.Item;";
                    //query = string.Format(query, itemCodeOrName, branch, dateTime.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE);
                    query = "SELECT BD.Item AS Code, MI.ItemName, MI.SellingPrice AS Price, ROUND(SUM(Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales FROM {4} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE (BD.Item like '{0}%' OR MI.ItemName like '{0}%') ";
                    if (branch != "0")
                        query += " AND BD.Branch = {1} ";
                    query += " AND DATEDIFF(DAY, '{2}', BD.Date) = 0 GROUP BY BD.Item, MI.ItemName, MI.SellingPrice ORDER BY BD.Item;";
                    query = string.Format(query, itemCodeOrName, branch, dateTime.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE);
                }
                else
                {
                    query = "SELECT BD.Item AS Code, MI.ItemName, MI.SellingPrice AS Price, ROUND(SUM(Qty),2) AS Qty, ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales FROM {5} BD JOIN MasterItems MI ON BD.Item = MI.ItemCode WHERE (BD.Item like '{0}%' OR MI.ItemName like '{0}%') ";
                    if (branch != "0")
                        query += " AND BD.Branch = {1} ";
                    query += " AND MONTH(BD.Date) = {2} AND YEAR(BD.Date) = {3} GROUP BY BD.Item, MI.ItemName, MI.SellingPrice ORDER BY BD.Item";
                    query = string.Format(query, itemCodeOrName, branch, dateTime.Month, dateTime.Year, BILLTABLE, BILLDETAILTABLE);
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(query, con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            return null;
        }

        internal static DataTable GetDeptWiseReportByBranch(string branch, DateTime date, string deptCode, bool isMonthly)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                if (IsBranchData)
                {
                    if (branch == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branch == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branch == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branch == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branch == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branch == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branch == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branch == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branch == "9")
                        con = new SqlConnection(sangareddyCon);
                }
                else
                {
                    con = new SqlConnection(mainCS);
                }

                string query = string.Empty;

                if (!isMonthly)
                {
                    //query = "SELECT BD.Item AS Code, MI.ItemName, MI.Dept, D.DeptName, MB.BranchName AS Branch, MI.SellingPrice AS Price, ROUND(SUM(BD.Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales  FROM {4} BD ";
                    //query += " JOIN MasterItems MI ON BD.Item = MI.ItemCode ";
                    //query += " JOIN {3} B ON BD.BillNo = B.BillNo ";
                    //query += " JOIN Department D ON MI.Dept = D.DeptID ";
                    //query += " JOIN MasterBranches MB ON MB.BranchID = BD.Branch ";
                    //query += " WHERE B.IsBillCancelled = 0 ";

                    //if (deptCode != "0")
                    //    query += " AND D.DeptID = {0} AND MI.Dept = {0} ";

                    //if (branch != "0")
                    //    query += " AND BD.Branch = {1} AND B.Branch = {1} ";

                    //query += " AND DATEDIFF(DAY, '{2}', BD.Date) = 0 AND DATEDIFF(DAY, '{2}', B.BillDate) = 0 GROUP BY BD.Item, MI.ItemName, MI.Dept, MB.BranchName, D.DeptName, MI.SellingPrice ORDER BY MI.Dept, BD.Item ";
                    //query = string.Format(query, deptCode, branch, date.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE);

                    query = "SELECT BD.Item AS Code, MI.ItemName, MI.Dept, D.DeptName, MB.BranchName AS Branch, MI.SellingPrice AS Price, ROUND(SUM(BD.Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales  FROM {4} BD ";
                    query += " JOIN MasterItems MI ON BD.Item = MI.ItemCode ";
                    //query += " JOIN {3} B ON BD.BillNo = B.BillNo ";
                    query += " JOIN Department D ON MI.Dept = D.DeptID ";
                    query += " JOIN MasterBranches MB ON MB.BranchID = BD.Branch ";
                    query += " WHERE DATEDIFF(DAY, '{2}', BD.Date) = 0 ";

                    if (deptCode != "0")
                        query += " AND D.DeptID = {0} AND MI.Dept = {0} ";

                    if (branch != "0")
                        query += " AND BD.Branch = {1} ";

                    query += " GROUP BY BD.Item, MI.ItemName, MI.Dept, MB.BranchName, D.DeptName, MI.SellingPrice ORDER BY MI.Dept, BD.Item ";
                    query = string.Format(query, deptCode, branch, date.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE);
                }
                else
                {
                    //query = "SELECT BD.Item AS Code, MI.ItemName, MI.Dept, D.DeptName, MB.BranchName AS Branch, MI.SellingPrice AS Price, ROUND(SUM(BD.Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales FROM {5} BD ";
                    //query += " JOIN MasterItems MI ON BD.Item = MI.ItemCode ";
                    //query += " JOIN {4} B ON BD.BillNo = B.BillNo ";
                    //query += " JOIN Department D ON MI.Dept = D.DeptID ";
                    //query += " JOIN MasterBranches MB ON MB.BranchID = BD.Branch ";
                    //query += " WHERE B.IsBillCancelled = 0 ";

                    //if (deptCode != "0")
                    //    query += " AND D.DeptID = {0} AND MI.Dept = {0} ";

                    //if (branch != "0")
                    //    query += " AND BD.Branch = {1} AND B.Branch = {1} ";

                    //query += " AND MONTH(BD.Date) = {2} AND YEAR(BD.Date) = {3} GROUP BY BD.Item, MI.ItemName, MI.Dept, MB.BranchName, D.DeptName, MI.SellingPrice ORDER BY MI.Dept, BD.Item ";
                    //query = string.Format(query, deptCode, branch, date.Month, date.Year, BILLTABLE, BILLDETAILTABLE);

                    query = "SELECT BD.Item AS Code, MI.ItemName, MI.Dept, D.DeptName, MB.BranchName AS Branch, MI.SellingPrice AS Price, ROUND(SUM(BD.Qty),2) AS 'Qty', ROUND(SUM(Qty * MI.SellingPrice),2) AS Sales FROM {5} BD ";
                    query += " JOIN MasterItems MI ON BD.Item = MI.ItemCode ";
                    //query += " JOIN {4} B ON BD.BillNo = B.BillNo ";
                    query += " JOIN Department D ON MI.Dept = D.DeptID ";
                    query += " JOIN MasterBranches MB ON MB.BranchID = BD.Branch ";
                    query += " WHERE MONTH(BD.Date) = {2} AND YEAR(BD.Date) = {3} ";

                    if (deptCode != "0")
                        query += " AND D.DeptID = {0} AND MI.Dept = {0} ";

                    if (branch != "0")
                        query += " AND BD.Branch = {1} ";

                    query += " GROUP BY BD.Item, MI.ItemName, MI.Dept, MB.BranchName, D.DeptName, MI.SellingPrice ORDER BY MI.Dept, BD.Item ";
                    query = string.Format(query, deptCode, branch, date.Month, date.Year, BILLTABLE, BILLDETAILTABLE);
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(query, con))
                {
                    DataTable ds = new DataTable();
                    adapter.Fill(ds);
                    if (ds != null && ds.Rows.Count > 0)
                        return ds;
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            return null;
        }

        internal static DataSet GetBillsByBranchAndPreviousBalance(string selectedDate, string branch, string supplier)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                string str = "SELECT BillID, SupplierID, BillNo, PaymentAmount AS Amount, CONVERT(VARCHAR, BillDate, 106) AS Date, IsValid FROM SupplierBillDetails WHERE Branch = {0} ";
                DateTime dt = DateTime.Parse(selectedDate);
                
                if (dt.Day == 1)
                    str += " AND DAY(BillDate) >= 1 AND DAY(BillDate) <=15 ";
                else
                    str += " AND DAY(BillDate) >= 16 AND DAY(BillDate) <= 31";
                str += " AND MONTH(BillDate) = {1} AND YEAR(BillDate) = {2} AND SupplierID = {3};";

                if (dt.Day == 1)
                    str += "SELECT Balance FROM SupplierPayments WHERE DAY(Month) = 16 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Supplier = {3} AND Branch = {0};";
                else
                    str += "SELECT Balance FROM SupplierPayments WHERE DAY(Month) = 1 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Supplier = {3} AND Branch = {0};";

                SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str, branch, dt.Month, dt.Year, supplier, (dt.Day == 1) ? dt.AddMonths(-1).Month : dt.Month, (dt.Day == 1) ? dt.AddMonths(-1).Year : dt.Year), financialCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds;
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetVendorSuppliers()
        {
            //SqlConnection con = null;

            try
            {
                //con = new SqlConnection(financialCS);
                //if (con.State == ConnectionState.Closed)
                //    con.Open();

                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Vendor FROM MasterVendors", mainCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                //if (con != null)
                //{
                //    con.Close();
                //    con.Dispose();
                //}
            }
            return null;
        }

        internal static int UpdateSupplierBill(string billNo, bool isValid)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE SupplierBillDetails SET IsValid = '{1}' WHERE BillID = {0}", billNo, isValid), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return 0;
        }

        internal static DataTable GetSelectedMonthPaymentDataByBranchAndSupplier(string selectedDate, string branch, string supplier)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                string str = "SELECT PaymentID, BillAmount, CarryForwarded, AmountPaid, Balance FROM SupplierPayments WHERE Branch = {0} AND Supplier = {1} AND DAY(MONTH) = {2} AND MONTH(MONTH) = {3} AND YEAR(MONTH) = {4}";
                DateTime dt = DateTime.Parse(selectedDate);

                SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str, branch, supplier, dt.Day, dt.Month, dt.Year), financialCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static int SavePaymentDetails(string branch, string supplier, string date, string previousBalance, string billsAmount, string amountPaid, string forwardBalance)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO [SupplierPayments] ([Month], [BillAmount], [CarryForwarded], [AmountPaid], [Balance], [Branch], [Supplier]) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')", DateTime.Parse(date).ToString("MM/dd/yyyy"), billsAmount, previousBalance, amountPaid, forwardBalance, branch, supplier), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return 0;
        }

        internal static DataTable GetSalesReport(DateTime date, bool isMontly, string department)
        {
            SqlConnection con = null;

            try
            {
                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == date.Month && DateTime.Now.Year == date.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", date.ToString("MMM"), date.Year.ToString());
                }

                con = new SqlConnection(mainCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                StringBuilder str = new StringBuilder();
                DataSet ds = new DataSet();

                if (isMontly)
                {
                    if (department == "0")
                    {
                        str.Append("SELECT '{0}';");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 1;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 1 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 2;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 2 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 3;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 3 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 4;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 4 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 5;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 5 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        string actualSQL = string.Empty;
                        DateTime dtSet = new DateTime(date.Year, date.Month, 1);

                        for (int cnt = 1; cnt <= 31; cnt++)
                        {
                            actualSQL += string.Format(str.ToString(), dtSet.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE);
                            dtSet = dtSet.AddDays(1);
                            if (date.Month != dtSet.Month)
                            {
                                dtSet = new DateTime(date.Year, date.Month, 1);
                                break;
                            }
                        }

                        DataTable dtValues = new DataTable();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();

                        SqlDataAdapter adapter = new SqlDataAdapter(actualSQL, mainCS);
                        adapter.Fill(ds);
                        double actualValue = 0d;
                        double tempValue = 0d;

                        double chandanagarColTotal = 0d;
                        double miyapurColTotal = 0d;
                        double kukatpallyColTotal = 0d;
                        double ameerpetColTotal = 0d;
                        double jntuColTotal = 0d;

                        for (int cnt = 0; cnt < ds.Tables.Count; cnt++)
                        {
                            double rowTotal = 0d;
                            DataRow row = dtValues.NewRow();
                            try
                            {
                                row[0] = dtSet.ToString("dd-MMM-yyyy");
                                dtSet = dtSet.AddDays(1);
                            }
                            catch (Exception) 
                            {
                                break;
                            }
                            //row[0] = new DateTime(date.Year, date.Month, dtCnt + 1).ToString("dd/MMM/yyyy");
                            
                            cnt++;
                            //Chandanagar
                            actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            cnt++;

                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            row[1] = actualValue;
                            rowTotal += actualValue;
                            chandanagarColTotal += actualValue;
                            cnt++;

                            //Miyapur
                            actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            cnt++;

                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            row[2] = actualValue;
                            rowTotal += actualValue;
                            miyapurColTotal += actualValue;
                            cnt++;

                            //Kukatpally
                            actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            cnt++;

                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            row[3] = actualValue;
                            rowTotal += actualValue;
                            kukatpallyColTotal += actualValue;
                            cnt++;

                            //Ameerpet
                            actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            cnt++;

                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            row[4] = actualValue;
                            rowTotal += actualValue;
                            ameerpetColTotal += actualValue;
                            cnt++;

                            //Jntu
                            actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            cnt++;

                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;
                            row[5] = actualValue;
                            rowTotal += actualValue;
                            jntuColTotal += actualValue;
                            row[6] = rowTotal;
                            //cnt++;
                            dtValues.Rows.Add(row);
                        }

                        DataRow totalRow = dtValues.NewRow();
                        totalRow[0] = "TOTAL";
                        totalRow[1] = chandanagarColTotal;
                        totalRow[2] = miyapurColTotal;
                        totalRow[3] = kukatpallyColTotal;
                        totalRow[4] = ameerpetColTotal;
                        totalRow[5] = jntuColTotal;
                        totalRow[6] = chandanagarColTotal + miyapurColTotal + kukatpallyColTotal + ameerpetColTotal + jntuColTotal;
                        dtValues.Rows.Add(totalRow);

                        return dtValues;
                    }
                    else
                    {
                        str.Append("SELECT '{0}';");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 1;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 2;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 3;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 4;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 5;");

                        string actualSQL = string.Empty;
                        DateTime dtSet = new DateTime(date.Year, date.Month, 1);

                        for (int cnt = 1; cnt <= 31; cnt++)
                        {
                            actualSQL += string.Format(str.ToString(), dtSet.ToString("MM/dd/yyyy"), department, BILLTABLE, BILLDETAILTABLE);
                            dtSet = dtSet.AddDays(1);
                            if (date.Month != dtSet.Month)
                            {
                                dtSet = new DateTime(date.Year, date.Month, 1);
                                break;
                            }
                        }

                        DataTable dtValues = new DataTable();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();
                        dtValues.Columns.Add();

                        SqlDataAdapter adapter = new SqlDataAdapter(actualSQL, mainCS);
                        adapter.Fill(ds);
                        //double actualValue = 0d;
                        double tempValue = 0d;

                        double chandanagarColTotal = 0d;
                        double miyapurColTotal = 0d;
                        double kukatpallyColTotal = 0d;
                        double ameerpetColTotal = 0d;
                        double jntuColTotal = 0d;

                        for (int cnt = 0; cnt < ds.Tables.Count; cnt++)
                        {
                            double rowTotal = 0d;
                            DataRow row = dtValues.NewRow();
                            try
                            {
                                row[0] = dtSet.ToString("dd-MMM-yyyy");
                                dtSet = dtSet.AddDays(1);
                            }
                            catch (Exception)
                            {
                                break;
                            }
                            //row[0] = new DateTime(date.Year, date.Month, dtCnt + 1).ToString("dd/MMM/yyyy");

                            cnt++;
                            //Chandanagar
                            //actualValue = 0d;
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            //actualValue += tempValue;
                            row[1] = tempValue;
                            rowTotal += tempValue;
                            chandanagarColTotal += tempValue;
                            cnt++;

                            //Miyapur
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            //actualValue += tempValue;
                            row[2] = tempValue;
                            rowTotal += tempValue;
                            miyapurColTotal += tempValue;
                            cnt++;

                            //Kukatpally
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            //actualValue += tempValue;
                            row[3] = tempValue;
                            rowTotal += tempValue;
                            kukatpallyColTotal += tempValue;
                            cnt++;

                            //Ameerpet
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            //actualValue += tempValue;
                            row[4] = tempValue;
                            rowTotal += tempValue;
                            ameerpetColTotal += tempValue;
                            cnt++;

                            //Jntu
                            tempValue = 0d;
                            if (ds.Tables[cnt] != null && ds.Tables[cnt].Rows.Count > 0 && ds.Tables[cnt].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[cnt].Rows[0][0].ToString(), out tempValue);
                            }
                            //actualValue += tempValue;
                            row[5] = tempValue;
                            rowTotal += tempValue;
                            jntuColTotal += tempValue;

                            row[6] = rowTotal;

                            dtValues.Rows.Add(row);
                        }

                        DataRow totalRow = dtValues.NewRow();
                        totalRow[0] = "TOTAL";
                        totalRow[1] = chandanagarColTotal;
                        totalRow[2] = miyapurColTotal;
                        totalRow[3] = kukatpallyColTotal;
                        totalRow[4] = ameerpetColTotal;
                        totalRow[5] = jntuColTotal;
                        totalRow[6] = chandanagarColTotal + miyapurColTotal + kukatpallyColTotal + ameerpetColTotal + jntuColTotal;
                        dtValues.Rows.Add(totalRow);

                        return dtValues;
                    }
                }
                else
                {
                    if (department == "0")
                    {
                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 1;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 1 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 2;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 2 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 3;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 3 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 4;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 4 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        str.Append("SELECT SUM(Qty) FROM {2} BD JOIN {1} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept IN (7,8) AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 5;");
                        str.Append("SELECT SUM(Qty) AS Qty FROM Cake WHERE IsCancelled = 0 AND Branch = 5 AND ((DeliveredDate IS NULL AND DATEDIFF(DD, DeliveryDate, '{0}') = 0) OR (DeliveredDate IS NOT NULL AND DATEDIFF(DD, DeliveredDate, '{0}') = 0));");

                        SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str.ToString(), date.ToString("MM/dd/yyyy"), BILLTABLE, BILLDETAILTABLE), mainCS);
                        adapter.Fill(ds);

                        double total = 0d;

                        if (ds != null && ds.Tables.Count == 10)
                        {
                            DataTable dtValues = new DataTable();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            
                            DataRow row1 = dtValues.NewRow();
                            row1[0] = date.ToString("dd/MMM/yyyy");

                            double tempValue = 0d;
                            double actualValue = 0d;

                            //Chandanagar
                            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[0].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;

                            if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0 && ds.Tables[1].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[1].Rows[0][0].ToString(), out tempValue);
                                actualValue += tempValue;
                            }
                            row1[1] = actualValue;
                            total += actualValue;

                            //Miyapur
                            actualValue = 0d;

                            if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0 && ds.Tables[2].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[2].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;

                            if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0 && ds.Tables[3].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[3].Rows[0][0].ToString(), out tempValue);
                                actualValue += tempValue;
                            }
                            row1[2] = actualValue;
                            total += actualValue;

                            //Kukatpally
                            actualValue = 0d;

                            if (ds.Tables[4] != null && ds.Tables[4].Rows.Count > 0 && ds.Tables[4].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[4].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;

                            if (ds.Tables[5] != null && ds.Tables[5].Rows.Count > 0 && ds.Tables[5].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[5].Rows[0][0].ToString(), out tempValue);
                                actualValue += tempValue;
                            }
                            row1[3] = actualValue;
                            total += actualValue;

                            //Ameerpet
                            actualValue = 0d;

                            if (ds.Tables[6] != null && ds.Tables[6].Rows.Count > 0 && ds.Tables[6].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[6].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;

                            if (ds.Tables[7] != null && ds.Tables[7].Rows.Count > 0 && ds.Tables[7].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[7].Rows[0][0].ToString(), out tempValue);
                                actualValue += tempValue;
                            }
                            row1[4] = actualValue;
                            total += actualValue;

                            //JNTU
                            actualValue = 0d;

                            if (ds.Tables[8] != null && ds.Tables[8].Rows.Count > 0 && ds.Tables[8].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[8].Rows[0][0].ToString(), out tempValue);
                            }
                            actualValue += tempValue;

                            if (ds.Tables[9] != null && ds.Tables[9].Rows.Count > 0 && ds.Tables[9].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[9].Rows[0][0].ToString(), out tempValue);
                                actualValue += tempValue;
                            }
                            row1[5] = actualValue;
                            total += actualValue;

                            row1[6] = total;

                            dtValues.Rows.Add(row1);
                            return dtValues;
                        }
                    }
                    else
                    {
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 1;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 2;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 3;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 4;");
                        str.Append(" SELECT SUM(Qty) FROM {3} BD JOIN {2} B ON BD.BillNo = B.BillNo JOIN MasterItems MI ON MI.ItemCode = BD.Item WHERE MI.Dept = {1} AND B.IsBillCancelled = 0 AND DATEDIFF(DD, BD.Date, '{0}') = 0 AND BD.Branch = 5;");

                        SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str.ToString(), date.ToString("MM/dd/yyyy"), department, BILLTABLE, BILLDETAILTABLE), mainCS);
                        adapter.Fill(ds);

                        double total = 0d;
                        if (ds != null && ds.Tables.Count == 5)
                        {
                            DataTable dtValues = new DataTable();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();
                            dtValues.Columns.Add();

                            DataRow row1 = dtValues.NewRow();
                            row1[0] = date.ToString("dd/MMM/yyyy");

                            double tempValue = 0d;

                            if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[0].Rows[0][0].ToString(), out tempValue);
                            }
                            row1[1] = tempValue;
                            total += tempValue;

                            if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0 && ds.Tables[1].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[1].Rows[0][0].ToString(), out tempValue);
                            }
                            row1[2] = tempValue;
                            total += tempValue;

                            if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0 && ds.Tables[2].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[2].Rows[0][0].ToString(), out tempValue);
                            }
                            row1[3] = tempValue;
                            total += tempValue;

                            if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0 && ds.Tables[3].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[3].Rows[0][0].ToString(), out tempValue);
                            }
                            row1[4] = tempValue;
                            total += tempValue;

                            if (ds.Tables[4] != null && ds.Tables[4].Rows.Count > 0 && ds.Tables[4].Rows[0][0] != null)
                            {
                                double.TryParse(ds.Tables[4].Rows[0][0].ToString(), out tempValue);
                            }
                            row1[5] = tempValue;
                            total += tempValue;

                            row1[6] = total;

                            dtValues.Rows.Add(row1);
                            return dtValues;
                        }
                    }
                }

                string s = string.Format(str.ToString(), date.ToString("MM/dd/yyyy"));

                //DateTime dt = DateTime.Parse(selectedDate);

                //SqlDataAdapter adapter = new SqlDataAdapter(str, mainCS);
                //adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetSelectedMonthCreditNoteDataByBranchAndCompany(string selectedDate, string branch, string company)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                string str = "SELECT PaymentID, BillAmount, CarryForwarded, AmountPaid, Balance FROM CreditNotePayments WHERE Branch = {0} AND Company = {1} AND DAY(MONTH) = {2} AND MONTH(MONTH) = {3} AND YEAR(MONTH) = {4}";
                DateTime dt = DateTime.Parse(selectedDate);

                SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str, branch, company, dt.Day, dt.Month, dt.Year), financialCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static DataTable GetCreditNotesByBranchAndPreviousBalance(string selectedDate, string branch, string company)
        {
            //SqlConnection con = null;

            try
            {
                //con = new SqlConnection(mainCS);
                //if (con.State == ConnectionState.Closed)
                //    con.Open();

                DataSet ds = new DataSet();
                string str = "SELECT CN.ID, CN.BillNo, CN.Amount, CONVERT(VARCHAR, CN.Date, 106) AS Date, B.IsBillCancelled AS IsCancelled FROM CreditNote CN JOIN {6} B ON CN.BillNo = B.BillNo WHERE CN.Branch = {0} AND B.Branch = {0}  ";
                DateTime dt = DateTime.Parse(selectedDate);

                if (dt.Day == 1)
                    str += " AND DAY(Date) >= 1 AND DAY(Date) <=15 ";
                else
                    str += " AND DAY(Date) >= 16 AND DAY(Date) <= 31";
                str += " AND MONTH(Date) = {1} AND YEAR(Date) = {2} AND CreditNoteCompany = {3};";

                //if (dt.Day == 1)
                //    str += "SELECT Balance FROM CreditNotePayments WHERE DAY(Month) = 16 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Company = {3} AND Branch = {0};";
                //else
                //    str += "SELECT Balance FROM CreditNotePayments WHERE DAY(Month) = 1 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Company = {3} AND Branch = {0};";

                string BILLTABLE = "Bill";
                string BILLDETAILTABLE = "BillDetail";

                if (!(DateTime.Now.Month == dt.Month && DateTime.Now.Year == dt.Year))
                {
                    BILLTABLE = string.Format("Bill_{0}_{1}", dt.ToString("MMM"), dt.Year.ToString());
                    BILLDETAILTABLE = string.Format("BillDetail_{0}_{1}", dt.ToString("MMM"), dt.Year.ToString());
                }

                SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str, branch, dt.Month, dt.Year, company, (dt.Day == 1) ? dt.AddMonths(-1).Month : dt.Month, (dt.Day == 1) ? dt.AddMonths(-1).Year : dt.Year, BILLTABLE), mainCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                //if (con != null)
                //{
                //    con.Close();
                //    con.Dispose();
                //}
            }
            return null;
        }

        internal static DataTable GetCreditNotesPreviousBalance(string selectedDate, string branch, string company)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                DataSet ds = new DataSet();
                string str = string.Empty;
                DateTime dt = DateTime.Parse(selectedDate);

                if (dt.Day == 1)
                    str += "SELECT Balance FROM CreditNotePayments WHERE DAY(Month) = 16 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Company = {3} AND Branch = {0};";
                else
                    str += "SELECT Balance FROM CreditNotePayments WHERE DAY(Month) = 1 AND MONTH(Month) = {4} AND YEAR(Month) = {5} AND Company = {3} AND Branch = {0};";

                SqlDataAdapter adapter = new SqlDataAdapter(string.Format(str, branch, dt.Month, dt.Year, company, (dt.Day == 1) ? dt.AddMonths(-1).Month : dt.Month, (dt.Day == 1) ? dt.AddMonths(-1).Year : dt.Year), financialCS);
                adapter.Fill(ds);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
                    return ds.Tables[0];
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        //TODO : Need to update Bill table
        internal static int UpdateCreditNoteBill(string billNo, bool isValid)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(mainCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("UPDATE Bill SET IsBillCancelled = '{1}' WHERE BillNo = '{0}'", billNo, isValid), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return 0;
        }

        internal static int SaveCreditNotePaymentDetails(string branch, string company, string date, string previousBalance, string billsAmount, string amountPaid, string forwardBalance)
        {
            SqlConnection con = null;

            try
            {
                con = new SqlConnection(financialCS);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                using (SqlCommand cmd = new SqlCommand(string.Format("INSERT INTO [CreditNotePayments] ([Month], [BillAmount], [CarryForwarded], [AmountPaid], [Balance], [Branch], [Company]) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')", DateTime.Parse(date).ToString("MM/dd/yyyy"), billsAmount, previousBalance, amountPaid, forwardBalance, branch, company), con))
                {
                    cmd.CommandType = CommandType.Text;
                    return cmd.ExecuteNonQuery();
                }
            }
            catch
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return 0;
        }

        internal static DataSet GetAcemadicReport(DateTime fromDate, DateTime toDate, string branchID)
        {
            SqlConnection con = null;
            try
            {
                StringBuilder str = new StringBuilder();

                //POS Sales
                str.Append("SELECT D.DeptCode, D.DeptName, ROUND(SUM(BD.Qty),2) AS Qty, ROUND(SUM (BD.Qty * MI.SellingPrice),2) AS Sales FROM BillDetail BD JOIN MasterItems MI ON BD.Item = MI.ItemCode RIGHT JOIN Department D ON MI.Dept = D.DeptID JOIN Bill B ON B.BillNo = BD.BillNo WHERE BD.Date >= '{0}' AND BD.Date <= '{1}' AND B.IsBillCancelled = 0 AND B.IsSpecialBill = 0 ");
                if (branchID != "0")
                    str.Append(" AND BD.Branch = {2} AND B.Branch = {2} GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");
                else
                    str.Append(" GROUP BY ALL D.DeptName, D.DeptCode ORDER BY D.DeptCode;");

                //POS Total Sales & POS Discounts
                str.Append("SELECT ROUND(SUM(TotalAmount + SodexhoPayment + CardPayment),2) AS POSSales, ROUND(SUM(TotalAmount - TotalNetAmount),2) AS POSDiscounts FROM Bill WHERE Bill.BillDate >= '{0}' AND Bill.BillDate <= '{1}' AND IsBillCancelled = 0 AND IsSpecialBill = 0 ");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(" ;");

                //Cake Advances & Cake Discounts
                str.Append("SELECT ROUND(SUM(Advance + DiscountByCost), 2) AS Advance, ROUND(SUM(DiscountByCost),2) AS CakeDiscounts FROM Cake WHERE IsCancelled = 0 AND BookedDate >= '{0}' AND BookedDate <= '{1}' ");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Cake Balances
                str.Append("SELECT ROUND(SUM(Balance), 2) AS Balance FROM Cake WHERE IsCancelled = 0 AND ((DeliveredDate IS NULL AND DeliveryDate >= '{0}' AND DeliveryDate <= '{1}') OR (DeliveredDate IS NOT NULL AND DeliveredDate >= '{0}' AND DeliveredDate <= '{1}'))");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Expenses
                str.Append("SELECT ROUND(SUM(Amount),2) AS ExpenseAmount FROM Expense WHERE Date >= '{0}' AND Date <= '{1}'");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Sodexho & Creditcard Sales
                str.Append("SELECT SUM(SodexhoAmount) AS SodexhoAmount, SUM(CreditCardAmount) AS CreditCardAmount FROM Amounts WHERE Date >= '{0}' AND Date <= '{1}' ");
                if (branchID != "0")
                    str.Append(" AND Branch = {2};");
                else
                    str.Append(";");

                //Credit Note Amount
                str.Append("SELECT ROUND(SUM(Amount),2) AS CreditNoteAmount FROM CreditNote CN JOIN Bill B ON CN.BillNo = B.BillNo WHERE CN.Date >= '{0}' AND CN.Date <= '{1}' AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND CN.Branch = {2} AND B.Branch = {2};");
                else
                    str.Append(";");

                //Vendor Bills Amount - 7
                str.Append("SELECT ROUND(SUM(Amount),2) AS VendorSales FROM VendorSales VS JOIN Bill B ON B.BillNo = VS.BillNo WHERE VS.Date >= '{0}' AND VS.Date <= '{1}' AND B.IsBillCancelled = 0 ");
                if (branchID != "0")
                    str.Append(" AND VS.Branch = {2} AND B.Branch = {2};");
                else
                    str.Append(";");

                //Bulk Orders Amount - 8
                str.Append(" SELECT SUM(Advance) AS BulkOrderAdvance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND BO.BookedDate >= '{0}' AND BO.BookedDate <= '{1}' ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {2};");
                else
                    str.Append(";");

                // - 9
                str.Append(" SELECT SUM(Balance) AS BulkOrderBalance FROM BulkOrder BO WHERE BO.IsOrderCancelled = 0 AND BO.BookedDate >= '{0}' AND BO.BookedDate <= '{1}' ");
                if (branchID != "0")
                    str.Append(" AND BO.Branch = {2};");
                else
                    str.Append(";");

                if (IsBranchData)
                {
                    /*using (SqlCommand cmd = new SqlCommand(string.Format(str.ToString(), dt.Month, dt.Year, branchID), kukatpallyCS))
                    {
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }*/
                    if (branchID == "1")
                        con = new SqlConnection(chandanagarCS);
                    else if (branchID == "2")
                        con = new SqlConnection(miyapurCS);
                    else if (branchID == "3")
                        con = new SqlConnection(kukatpallyCS);
                    else if (branchID == "4")
                        con = new SqlConnection(ameerpetCS);
                    else if (branchID == "5")
                        con = new SqlConnection(jntuCS);
                    else if (branchID == "6")
                        con = new SqlConnection(csc1Con);
                    else if (branchID == "7")
                        con = new SqlConnection(csc2Con);
                    else if (branchID == "8")
                        con = new SqlConnection(csc3Con);
                    else if (branchID == "9")
                        con = new SqlConnection(sangareddyCon);

                    if (con.State == ConnectionState.Closed)
                        con.Open();

                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), branchID);
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
                else
                {
                    /*using (SqlCommand cmd = new SqlCommand(string.Format(str.ToString(), dt.Month, dt.Year, branchID), mainCS))
                    {
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }*/
                    con = new SqlConnection(mainCS);
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = string.Format(str.ToString(), fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), branchID);
                        cmd.CommandType = CommandType.Text;
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds);
                            return ds;
                        }
                    }
                }
            }
            catch (Exception)
            { }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            return null;
        }

        internal static int SaveActuals(DateTime date, string branch, DataGridView dgvIndividualSales)
        {
            SqlConnection con = null;
            SqlTransaction trans = null;

            try
            {
                con = new SqlConnection(mainCS);

                if (con.State == ConnectionState.Closed)
                    con.Open();
                trans = con.BeginTransaction();

                foreach (DataGridViewRow row in dgvIndividualSales.Rows)
                {
                    double cashAvailable = 0d;
                    double.TryParse(row.Cells["CashAvailable"].Value.ToString(), out cashAvailable);

                    double cashInHand = 0d;
                    double.TryParse(row.Cells["CashInHand"].Value.ToString(), out cashInHand);

                    using (SqlCommand cmd = new SqlCommand(string.Format("exec AddOrModifyAvailableCash '{0}', '{1}', '{2}', '{3}', '{4}', '{5}'", date.ToString("MM/dd/yyyy"), branch, row.Cells["Cashier"].Value.ToString(), cashAvailable, cashInHand, cashAvailable - cashInHand), con, trans))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
                trans.Commit();
                return 1;
            }
            catch (Exception)
            {
                trans.Rollback();
                return 0;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        internal static DataTable GetActuals(DateTime date, string branch, DataTable dt)
        {
            try
            {
                DataTable ds = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT Type, ActualCashAvailable, -1 * ROUND(Difference, 2) FROM AvailableCash WHERE DateDiff(dd, Date, '{0}') = 0 AND Branch = {1}", date.ToString("MM/dd/yyyy"), branch), mainCS);
                adapter.Fill(ds);
                if (ds != null && ds.Rows.Count > 0)
                    return ds;
            }
            catch (Exception)
            { }
            return null;
        }

        internal static bool ValidateVersion(int appID, int version)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(string.Format("SELECT ApplicationID FROM VersionManagement WHERE ApplicationID = {0} AND Version = {1}", appID, version), mainCS);
                adapter.Fill(dt);
                if (dt != null && dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception)
            { }
            return false;
        }
    }
}
